﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using PPAP.Model;
using System.Data.OracleClient;

namespace PPAP.DAO
{
    public class SignDAO
    {
        public SignDAO() { }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int  Add(Sign model)
        {
            model.ID = DbHelperOra.GetMaxID("ID", "SIGN");
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into SIGN(");
            strSql.Append("ID,PPARID,RECEIVEQUANTITY,RECEIVEDATE,SIGNREMARK,SIGNPROCESSOR)");
            strSql.Append(" values (");
            strSql.Append(":ID,:PPARID,:RECEIVEQUANTITY,:RECEIVEDATE,:SIGNREMARK,:SIGNPROCESSOR)");
            OracleParameter[] parameters = {
					new OracleParameter(":ID", OracleType.Number,4),
					new OracleParameter(":PPARID", OracleType.Number,4),
					new OracleParameter(":RECEIVEQUANTITY", OracleType.Number,4),
					new OracleParameter(":RECEIVEDATE", OracleType.DateTime),
					new OracleParameter(":SIGNREMARK", OracleType.VarChar,100),
					new OracleParameter(":SIGNPROCESSOR", OracleType.VarChar,50)};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.PPARID;
            parameters[2].Value = model.RECEIVEQUANTITY;
            parameters[3].Value = model.RECEIVEDATE;
            parameters[4].Value = model.SIGNREMARK;
            parameters[5].Value = model.SIGNPROCESSOR;

           return DbHelperOra.ExecuteSql(strSql.ToString(), parameters)>0?int.Parse(model.ID.ToString()):0;
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public int Update(Sign model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update SIGN set ");
            strSql.Append("ID=:ID,");
            strSql.Append("PPARID=:PPARID,");
            strSql.Append("RECEIVEQUANTITY=:RECEIVEQUANTITY,");
            strSql.Append("RECEIVEDATE=:RECEIVEDATE,");
            strSql.Append("SIGNREMARK=:SIGNREMARK,");
            strSql.Append("SIGNPROCESSOR=:SIGNPROCESSOR");
            strSql.Append(" where id="+model.ID);
            OracleParameter[] parameters = {
					new OracleParameter(":ID", OracleType.Number,4),
					new OracleParameter(":PPARID", OracleType.Number,4),
					new OracleParameter(":RECEIVEQUANTITY", OracleType.Number,4),
					new OracleParameter(":RECEIVEDATE", OracleType.DateTime),
					new OracleParameter(":SIGNREMARK", OracleType.VarChar,100),
					new OracleParameter(":SIGNPROCESSOR", OracleType.VarChar,50)};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.PPARID;
            parameters[2].Value = model.RECEIVEQUANTITY;
            parameters[3].Value = model.RECEIVEDATE;
            parameters[4].Value = model.SIGNREMARK;
            parameters[5].Value = model.SIGNPROCESSOR;

           return DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public int Delete(int id)
        {
            //该表无主键信息，请自定义主键/条件字段
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from SIGN ");
            strSql.Append(" where id="+id);
            OracleParameter[] parameters = {
};

           return DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Sign GetModel(int id)
        {
            //该表无主键信息，请自定义主键/条件字段
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,PPARID,RECEIVEQUANTITY,RECEIVEDATE,SIGNREMARK,SIGNPROCESSOR from SIGN ");
            strSql.Append(" where id="+id);
            OracleParameter[] parameters = {
};

            Sign model = new Sign();
            DataSet ds = DbHelperOra.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["PPARID"].ToString() != "")
                {
                    model.PPARID = int.Parse(ds.Tables[0].Rows[0]["PPARID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["RECEIVEQUANTITY"].ToString() != "")
                {
                    model.RECEIVEQUANTITY = int.Parse(ds.Tables[0].Rows[0]["RECEIVEQUANTITY"].ToString());
                }
                if (ds.Tables[0].Rows[0]["RECEIVEDATE"].ToString() != "")
                {
                    model.RECEIVEDATE = DateTime.Parse(ds.Tables[0].Rows[0]["RECEIVEDATE"].ToString());
                }
                model.SIGNREMARK = ds.Tables[0].Rows[0]["SIGNREMARK"].ToString();
                model.SIGNPROCESSOR = ds.Tables[0].Rows[0]["SIGNPROCESSOR"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Sign GetModelByPPARID(int id)
        {
            //该表无主键信息，请自定义主键/条件字段
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,PPARID,RECEIVEQUANTITY,RECEIVEDATE,SIGNREMARK,SIGNPROCESSOR from SIGN ");
            strSql.Append(" where PPARid=" + id);
            OracleParameter[] parameters = {
};

            Sign model = new Sign();
            DataSet ds = DbHelperOra.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["PPARID"].ToString() != "")
                {
                    model.PPARID = int.Parse(ds.Tables[0].Rows[0]["PPARID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["RECEIVEQUANTITY"].ToString() != "")
                {
                    model.RECEIVEQUANTITY = int.Parse(ds.Tables[0].Rows[0]["RECEIVEQUANTITY"].ToString());
                }
                if (ds.Tables[0].Rows[0]["RECEIVEDATE"].ToString() != "")
                {
                    model.RECEIVEDATE = DateTime.Parse(ds.Tables[0].Rows[0]["RECEIVEDATE"].ToString());
                }
                model.SIGNREMARK = ds.Tables[0].Rows[0]["SIGNREMARK"].ToString();
                model.SIGNPROCESSOR = ds.Tables[0].Rows[0]["SIGNPROCESSOR"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,PPARID,RECEIVEQUANTITY,RECEIVEDATE,SIGNREMARK,SIGNPROCESSOR ");
            strSql.Append(" FROM SIGN ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperOra.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        /// <returns></returns>
        public DataSet GetSignList()
        {
            string sql = @"select s.receivequantity,s.receivedate,s.signprocessor,'PPAR-'||p.pparno||'.'||PPARVERSION as pparno,l.suppliername,r.bdntpartno,r.bdntpartname,rownum,p.id,p.deliverycompany from ppar p left join part r on p.partid=r.id 
left join sign s on p.id=s.pparid left join supplier l on r.supplierid=l.id where p.status>=2 and p.status<>4  and iswithoutsample=0";
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        /// <returns></returns>
        public DataSet GetSignListForSearch(string sqlwhere)
        {
            string sql = @"select rownum,s.receivequantity,s.receivedate,s.signprocessor,'PPAR-'||p.pparno||'.'||PPARVERSION as pparno,l.suppliername,r.bdntpartno,r.bdntpartname,p.id,p.deliverycompany from ppar p left join part r on p.partid=r.id 
left join sign s on p.id=s.pparid left join supplier l on r.supplierid=l.id where p.status>=2 and p.status<>4  and iswithoutsample=0";
            sql += sqlwhere;
            return DbHelperOra.Query(sql);
        }
    }
}
